-- FlowPlan数据库完整表结构和注释文档（包含新增功能扩展）
-- 包含表结构定义、表注释和字段注释

-- ==========================================
-- 1. 组织表 (sys_org)
-- ==========================================

-- 表结构
CREATE TABLE sys_org (
    id BIGSERIAL PRIMARY KEY,
    org_name VARCHAR(100) NOT NULL,
    org_code VARCHAR(50) UNIQUE NOT NULL,
    parent_id BIGINT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES sys_org(id)
);

-- 表注释
COMMENT ON TABLE sys_org IS '组织架构表，存储公司或团队的组织结构信息';

-- 字段注释
COMMENT ON COLUMN sys_org.id IS '组织ID，主键，自增长';
COMMENT ON COLUMN sys_org.org_name IS '组织名称';
COMMENT ON COLUMN sys_org.org_code IS '组织编码，唯一标识符';
COMMENT ON COLUMN sys_org.parent_id IS '父组织ID，用于构建组织层级关系';
COMMENT ON COLUMN sys_org.create_time IS '创建时间';

-- ==========================================
-- 2. 用户表 (sys_user)
-- ==========================================

-- 表结构
CREATE TABLE sys_user (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'DEVELOPER',
    org_id BIGINT NOT NULL,
    avatar_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (org_id) REFERENCES sys_org(id)
);

-- 表注释
COMMENT ON TABLE sys_user IS '用户信息表，存储系统用户的基本信息和权限';

-- 字段注释
COMMENT ON COLUMN sys_user.id IS '用户ID，主键，自增长';
COMMENT ON COLUMN sys_user.username IS '用户名，用于登录系统的唯一标识';
COMMENT ON COLUMN sys_user.email IS '邮箱地址，用户的联系邮箱';
COMMENT ON COLUMN sys_user.password_hash IS '密码哈希值，存储加密后的用户密码';
COMMENT ON COLUMN sys_user.role IS '用户角色，决定用户的权限级别';
COMMENT ON COLUMN sys_user.org_id IS '所属组织ID，关联sys_org表';
COMMENT ON COLUMN sys_user.avatar_url IS '用户头像URL地址';
COMMENT ON COLUMN sys_user.is_active IS '账户状态，标识用户账户是否激活';
COMMENT ON COLUMN sys_user.create_time IS '账户创建时间';
COMMENT ON COLUMN sys_user.update_time IS '账户信息更新时间';

-- ==========================================
-- 3. 项目表 (project)
-- ==========================================

-- 表结构
CREATE TABLE project (
    id BIGSERIAL PRIMARY KEY,
    project_name VARCHAR(200) NOT NULL,
    description TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'PLANNING',
    priority VARCHAR(10) DEFAULT 'MEDIUM',
    creator_id BIGINT NOT NULL,
    deadline TIMESTAMP,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (creator_id) REFERENCES sys_user(id)
);

-- 表注释
COMMENT ON TABLE project IS '项目信息表，存储所有项目的基本信息和状态';

-- 字段注释
COMMENT ON COLUMN project.id IS '项目ID，主键，自增长';
COMMENT ON COLUMN project.project_name IS '项目名称';
COMMENT ON COLUMN project.description IS '项目描述信息';
COMMENT ON COLUMN project.status IS '项目状态，标识项目的当前进展阶段';
COMMENT ON COLUMN project.priority IS '项目优先级';
COMMENT ON COLUMN project.creator_id IS '项目创建人ID，关联sys_user表';
COMMENT ON COLUMN project.deadline IS '项目截止时间';
COMMENT ON COLUMN project.create_time IS '项目创建时间';
COMMENT ON COLUMN project.update_time IS '项目信息更新时间';

-- ==========================================
-- 4. Excel计划表 (excel_plan)
-- ==========================================

-- 表结构
CREATE TABLE excel_plan (
    id BIGSERIAL PRIMARY KEY,
    project_id BIGINT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT,
    sheet_names JSONB,
    demander VARCHAR(100),
    upload_user_id BIGINT NOT NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES project(id),
    FOREIGN KEY (upload_user_id) REFERENCES sys_user(id)
);

-- 表注释
COMMENT ON TABLE excel_plan IS 'Excel计划表，存储从Excel文件中提取的项目计划信息';

-- 字段注释
COMMENT ON COLUMN excel_plan.id IS 'Excel计划ID，主键，自增长';
COMMENT ON COLUMN excel_plan.project_id IS '关联项目ID，关联project表';
COMMENT ON COLUMN excel_plan.file_name IS 'Excel文件名';
COMMENT ON COLUMN excel_plan.file_path IS 'Excel文件存储路径';
COMMENT ON COLUMN excel_plan.file_size IS '文件大小，以字节为单位';
COMMENT ON COLUMN excel_plan.sheet_names IS 'Sheet页名称列表，JSON格式存储';
COMMENT ON COLUMN excel_plan.demander IS '需求提供方，说明需求的来源部门或人员';
COMMENT ON COLUMN excel_plan.upload_user_id IS '上传用户ID，关联sys_user表';
COMMENT ON COLUMN excel_plan.create_time IS '计划创建时间';
COMMENT ON COLUMN excel_plan.update_time IS '计划更新时间';

-- ==========================================
-- 5. 待办表 (todo)
-- ==========================================

-- 表结构
CREATE TABLE todo (
    id BIGSERIAL PRIMARY KEY,
    project_id BIGINT NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'TODO',
    priority VARCHAR(10) DEFAULT 'MEDIUM',
    todo_type VARCHAR(30) NOT NULL,
    assignee_id BIGINT,
    reporter_id BIGINT NOT NULL,
    due_date TIMESTAMP,
    estimated_hours DECIMAL(5,2),
    actual_hours DECIMAL(5,2),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES project(id),
    FOREIGN KEY (assignee_id) REFERENCES sys_user(id),
    FOREIGN KEY (reporter_id) REFERENCES sys_user(id)
);

-- 表注释
COMMENT ON TABLE todo IS '待办任务表，存储项目中的具体任务信息';

-- 字段注释
COMMENT ON COLUMN todo.id IS '待办任务ID，主键，自增长';
COMMENT ON COLUMN todo.project_id IS '关联项目ID，关联project表';
COMMENT ON COLUMN todo.title IS '待办任务标题';
COMMENT ON COLUMN todo.description IS '待办任务详细描述';
COMMENT ON COLUMN todo.status IS '待办任务状态';
COMMENT ON COLUMN todo.priority IS '待办任务优先级';
COMMENT ON COLUMN todo.todo_type IS '待办任务类型，区分不同性质的任务';
COMMENT ON COLUMN todo.assignee_id IS '任务负责人ID，关联sys_user表';
COMMENT ON COLUMN todo.reporter_id IS '任务报告人ID，关联sys_user表';
COMMENT ON COLUMN todo.due_date IS '任务截止日期';
COMMENT ON COLUMN todo.estimated_hours IS '预估工时，完成任务预计需要的时间';
COMMENT ON COLUMN todo.actual_hours IS '实际工时，完成任务实际花费的时间';
COMMENT ON COLUMN todo.create_time IS '任务创建时间';
COMMENT ON COLUMN todo.update_time IS '任务更新时间';

-- ==========================================
-- 6. Excel-待办关联表 (excel_todo_rel)
-- ==========================================

-- 表结构
CREATE TABLE excel_todo_rel (
    id BIGSERIAL PRIMARY KEY,
    excel_id BIGINT NOT NULL,
    todo_id BIGINT NOT NULL,
    relation_type VARCHAR(20) DEFAULT 'DERIVED',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (excel_id) REFERENCES excel_plan(id),
    FOREIGN KEY (todo_id) REFERENCES todo(id)
);

-- 表注释
COMMENT ON TABLE excel_todo_rel IS 'Excel计划与待办任务关联表，记录Excel计划与待办任务的对应关系';

-- 字段注释
COMMENT ON COLUMN excel_todo_rel.id IS '关联关系ID，主键，自增长';
COMMENT ON COLUMN excel_todo_rel.excel_id IS 'Excel计划ID，关联excel_plan表';
COMMENT ON COLUMN excel_todo_rel.todo_id IS '待办任务ID，关联todo表';
COMMENT ON COLUMN excel_todo_rel.relation_type IS '关联类型，标识Excel计划与待办任务的关系性质';
COMMENT ON COLUMN excel_todo_rel.create_time IS '关联关系创建时间';

-- ==========================================
-- 7. 笔记表 (note)
-- ==========================================

-- 表结构
CREATE TABLE note (
    id BIGSERIAL PRIMARY KEY,
    project_id BIGINT NOT NULL,
    todo_id BIGINT,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    note_type VARCHAR(20) DEFAULT 'GENERAL',
    tags VARCHAR(500),
    author_id BIGINT NOT NULL,
    is_public BOOLEAN DEFAULT FALSE,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES project(id),
    FOREIGN KEY (todo_id) REFERENCES todo(id),
    FOREIGN KEY (author_id) REFERENCES sys_user(id)
);

-- 表注释
COMMENT ON TABLE note IS '笔记表，存储项目和任务相关的笔记信息';

-- 字段注释
COMMENT ON COLUMN note.id IS '笔记ID，主键，自增长';
COMMENT ON COLUMN note.project_id IS '关联项目ID，关联project表';
COMMENT ON COLUMN note.todo_id IS '关联待办任务ID，关联todo表（可为空）';
COMMENT ON COLUMN note.title IS '笔记标题';
COMMENT ON COLUMN note.content IS '笔记内容，支持文本格式';
COMMENT ON COLUMN note.note_type IS '笔记类型，区分不同性质的笔记';
COMMENT ON COLUMN note.tags IS '笔记标签，用于分类和检索，逗号分隔';
COMMENT ON COLUMN note.author_id IS '笔记作者ID，关联sys_user表';
COMMENT ON COLUMN note.is_public IS '是否公开，标识笔记是否对所有项目成员可见';
COMMENT ON COLUMN note.create_time IS '笔记创建时间';
COMMENT ON COLUMN note.update_time IS '笔记更新时间';

-- ==========================================
-- 8. 审计日志表 (audit_log)
-- ==========================================

-- 表结构
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    operator_id BIGINT NOT NULL,
    operation_type VARCHAR(30) NOT NULL,
    resource_type VARCHAR(30) NOT NULL,
    resource_id BIGINT,
    old_value JSONB,
    new_value JSONB,
    ip_address INET,
    user_agent TEXT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (operator_id) REFERENCES sys_user(id)
);

-- 表注释
COMMENT ON TABLE audit_log IS '审计日志表，记录系统中的重要操作日志';

-- 字段注释
COMMENT ON COLUMN audit_log.id IS '日志ID，主键，自增长';
COMMENT ON COLUMN audit_log.operator_id IS '操作人ID，关联sys_user表';
COMMENT ON COLUMN audit_log.operation_type IS '操作类型，标识执行的操作种类';
COMMENT ON COLUMN audit_log.resource_type IS '资源类型，标识操作的对象类型';
COMMENT ON COLUMN audit_log.resource_id IS '资源ID，标识操作的具体对象';
COMMENT ON COLUMN audit_log.old_value IS '变更前的值，JSON格式存储';
COMMENT ON COLUMN audit_log.new_value IS '变更后的值，JSON格式存储';
COMMENT ON COLUMN audit_log.ip_address IS '操作发起的IP地址';
COMMENT ON COLUMN audit_log.user_agent IS '用户代理信息，记录客户端信息';
COMMENT ON COLUMN audit_log.create_time IS '日志创建时间';

-- ==========================================
-- 9. 离线操作记录表 (offline_operation)
-- ==========================================

-- 表结构
CREATE TABLE offline_operation (
    id BIGSERIAL PRIMARY KEY,
    entity_type VARCHAR(50) NOT NULL,
    operation_type VARCHAR(20) NOT NULL,
    entity_id BIGINT,
    operation_data JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced BOOLEAN DEFAULT FALSE,
    sync_attempts INTEGER DEFAULT 0,
    last_sync_error TEXT
);

-- 表注释
COMMENT ON TABLE offline_operation IS '离线操作记录表，存储用户在离线状态下执行的操作';

-- 字段注释
COMMENT ON COLUMN offline_operation.id IS '操作记录ID，主键，自增长';
COMMENT ON COLUMN offline_operation.entity_type IS '实体类型，标识操作的对象类型（如note, project, todo等）';
COMMENT ON COLUMN offline_operation.operation_type IS '操作类型（CREATE, UPDATE, DELETE）';
COMMENT ON COLUMN offline_operation.entity_id IS '实体ID，标识操作的具体对象（对于CREATE操作可能为空）';
COMMENT ON COLUMN offline_operation.operation_data IS '操作数据，JSON格式存储操作的详细信息';
COMMENT ON COLUMN offline_operation.created_at IS '操作创建时间';
COMMENT ON COLUMN offline_operation.synced IS '是否已同步，标识操作是否已成功同步到服务器';
COMMENT ON COLUMN offline_operation.sync_attempts IS '同步尝试次数';
COMMENT ON COLUMN offline_operation.last_sync_error IS '最后一次同步错误信息';

-- ==========================================
-- 10. 本地缓存表 (local_cache)
-- ==========================================

-- 表结构
CREATE TABLE local_cache (
    id BIGSERIAL PRIMARY KEY,
    cache_key VARCHAR(255) UNIQUE NOT NULL,
    cache_value JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    cache_type VARCHAR(50) DEFAULT 'GENERAL'
);

-- 表注释
COMMENT ON TABLE local_cache IS '本地缓存表，存储应用的本地缓存数据';

-- 字段注释
COMMENT ON COLUMN local_cache.id IS '缓存ID，主键，自增长';
COMMENT ON COLUMN local_cache.cache_key IS '缓存键，唯一标识缓存项';
COMMENT ON COLUMN local_cache.cache_value IS '缓存值，JSON格式存储缓存数据';
COMMENT ON COLUMN local_cache.created_at IS '缓存创建时间';
COMMENT ON COLUMN local_cache.expires_at IS '缓存过期时间';
COMMENT ON COLUMN local_cache.cache_type IS '缓存类型，用于分类管理缓存';

-- ==========================================
-- 11. 同步状态表 (sync_status)
-- ==========================================

-- 表结构
CREATE TABLE sync_status (
    id BIGSERIAL PRIMARY KEY,
    entity_type VARCHAR(50) NOT NULL,
    last_sync_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    sync_status VARCHAR(20) DEFAULT 'IDLE',
    error_message TEXT,
    next_sync_time TIMESTAMP
);

-- 表注释
COMMENT ON TABLE sync_status IS '同步状态表，记录各实体类型的同步状态';

-- 字段注释
COMMENT ON COLUMN sync_status.id IS '状态ID，主键，自增长';
COMMENT ON COLUMN sync_status.entity_type IS '实体类型，标识同步的对象类型';
COMMENT ON COLUMN sync_status.last_sync_time IS '最后同步时间';
COMMENT ON COLUMN sync_status.sync_status IS '同步状态（IDLE, SYNCING, COMPLETED, FAILED）';
COMMENT ON COLUMN sync_status.error_message IS '错误信息';
COMMENT ON COLUMN sync_status.next_sync_time IS '下次同步时间';

-- ==========================================
-- 12. 离线数据统计表 (offline_stats)
-- ==========================================

-- 表结构
CREATE TABLE offline_stats (
    id BIGSERIAL PRIMARY KEY,
    stat_date DATE NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    offline_count INTEGER DEFAULT 0,
    sync_pending_count INTEGER DEFAULT 0,
    sync_success_count INTEGER DEFAULT 0,
    sync_failed_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 表注释
COMMENT ON TABLE offline_stats IS '离线数据统计表，记录离线操作的统计数据';

-- 字段注释
COMMENT ON COLUMN offline_stats.id IS '统计ID，主键，自增长';
COMMENT ON COLUMN offline_stats.stat_date IS '统计日期';
COMMENT ON COLUMN offline_stats.entity_type IS '实体类型';
COMMENT ON COLUMN offline_stats.offline_count IS '离线操作数量';
COMMENT ON COLUMN offline_stats.sync_pending_count IS '待同步操作数量';
COMMENT ON COLUMN offline_stats.sync_success_count IS '同步成功操作数量';
COMMENT ON COLUMN offline_stats.sync_failed_count IS '同步失败操作数量';
COMMENT ON COLUMN offline_stats.created_at IS '记录创建时间';

-- ==========================================
-- 13. 数据库连接配置表 (db_connection)
-- ==========================================

-- 表结构
CREATE TABLE db_connection (
    id BIGSERIAL PRIMARY KEY,
    connection_name VARCHAR(100) NOT NULL,
    db_type VARCHAR(20) NOT NULL,
    host VARCHAR(255) NOT NULL,
    port INTEGER NOT NULL,
    database_name VARCHAR(100) NOT NULL,
    username VARCHAR(100) NOT NULL,
    password_encrypted VARCHAR(500) NOT NULL,
    project_id BIGINT NOT NULL,
    created_by BIGINT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES project(id),
    FOREIGN KEY (created_by) REFERENCES sys_user(id)
);

-- 表注释
COMMENT ON TABLE db_connection IS '数据库连接配置表，存储用户配置的外部数据库连接信息';

-- 字段注释
COMMENT ON COLUMN db_connection.id IS '连接配置ID，主键，自增长';
COMMENT ON COLUMN db_connection.connection_name IS '连接名称，用户自定义的连接标识';
COMMENT ON COLUMN db_connection.db_type IS '数据库类型（MYSQL, POSTGRESQL, SQLSERVER, SQLITE, MARIADB）';
COMMENT ON COLUMN db_connection.host IS '数据库主机地址';
COMMENT ON COLUMN db_connection.port IS '数据库端口号';
COMMENT ON COLUMN db_connection.database_name IS '数据库名称';
COMMENT ON COLUMN db_connection.username IS '数据库用户名';
COMMENT ON COLUMN db_connection.password_encrypted IS '加密后的数据库密码';
COMMENT ON COLUMN db_connection.project_id IS '关联项目ID，关联project表';
COMMENT ON COLUMN db_connection.created_by IS '创建人ID，关联sys_user表';
COMMENT ON COLUMN db_connection.is_active IS '是否激活，标识连接配置是否可用';
COMMENT ON COLUMN db_connection.created_at IS '创建时间';
COMMENT ON COLUMN db_connection.updated_at IS '更新时间';

-- ==========================================
-- 14. 数据表映射配置表 (table_mapping)
-- ==========================================

-- 表结构
CREATE TABLE table_mapping (
    id BIGSERIAL PRIMARY KEY,
    connection_id BIGINT NOT NULL,
    source_table_name VARCHAR(100) NOT NULL,
    target_table_name VARCHAR(100) NOT NULL,
    mapping_config JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (connection_id) REFERENCES db_connection(id)
);

-- 表注释
COMMENT ON TABLE table_mapping IS '数据表映射配置表，存储源数据库表与系统表的映射关系';

-- 字段注释
COMMENT ON COLUMN table_mapping.id IS '映射配置ID，主键，自增长';
COMMENT ON COLUMN table_mapping.connection_id IS '连接配置ID，关联db_connection表';
COMMENT ON COLUMN table_mapping.source_table_name IS '源数据库表名';
COMMENT ON COLUMN table_mapping.target_table_name IS '目标系统表名';
COMMENT ON COLUMN table_mapping.mapping_config IS '映射配置，JSON格式存储字段映射关系';
COMMENT ON COLUMN table_mapping.created_at IS '创建时间';
COMMENT ON COLUMN table_mapping.updated_at IS '更新时间';

-- ==========================================
-- 15. 数据同步记录表 (data_sync_record)
-- ==========================================

-- 表结构
CREATE TABLE data_sync_record (
    id BIGSERIAL PRIMARY KEY,
    connection_id BIGINT NOT NULL,
    table_name VARCHAR(100) NOT NULL,
    sync_type VARCHAR(20) NOT NULL,
    record_count INTEGER,
    success_count INTEGER DEFAULT 0,
    fail_count INTEGER DEFAULT 0,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    status VARCHAR(20) DEFAULT 'PENDING',
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (connection_id) REFERENCES db_connection(id)
);

-- 表注释
COMMENT ON TABLE data_sync_record IS '数据同步记录表，记录数据库同步操作的历史';

-- 字段注释
COMMENT ON COLUMN data_sync_record.id IS '同步记录ID，主键，自增长';
COMMENT ON COLUMN data_sync_record.connection_id IS '连接配置ID，关联db_connection表';
COMMENT ON COLUMN data_sync_record.table_name IS '同步的表名';
COMMENT ON COLUMN data_sync_record.sync_type IS '同步类型（FULL, INCREMENTAL）';
COMMENT ON COLUMN data_sync_record.record_count IS '记录总数';
COMMENT ON COLUMN data_sync_record.success_count IS '成功记录数';
COMMENT ON COLUMN data_sync_record.fail_count IS '失败记录数';
COMMENT ON COLUMN data_sync_record.start_time IS '同步开始时间';
COMMENT ON COLUMN data_sync_record.end_time IS '同步结束时间';
COMMENT ON COLUMN data_sync_record.status IS '同步状态（PENDING, RUNNING, COMPLETED, FAILED）';
COMMENT ON COLUMN data_sync_record.error_message IS '错误信息';
COMMENT ON COLUMN data_sync_record.created_at IS '创建时间';

-- ==========================================
-- 索引定义
-- ==========================================

-- 用户相关索引
CREATE INDEX idx_user_org_id ON sys_user(org_id);
CREATE INDEX idx_user_role ON sys_user(role);
CREATE UNIQUE INDEX uk_user_username ON sys_user(username);
CREATE UNIQUE INDEX uk_user_email ON sys_user(email);

-- 组织相关索引
CREATE UNIQUE INDEX uk_org_code ON sys_org(org_code);
CREATE INDEX idx_org_parent_id ON sys_org(parent_id);

-- 项目相关索引
CREATE INDEX idx_project_creator_id ON project(creator_id);
CREATE INDEX idx_project_status ON project(status);
CREATE INDEX idx_project_deadline ON project(deadline) WHERE deadline IS NOT NULL;

-- Excel计划索引
CREATE INDEX idx_excel_plan_project_id ON excel_plan(project_id);
CREATE INDEX idx_excel_plan_upload_user ON excel_plan(upload_user_id);

-- 待办相关索引
CREATE INDEX idx_todo_project_status ON todo(project_id, status);
CREATE INDEX idx_todo_assignee_id ON todo(assignee_id);
CREATE INDEX idx_todo_type ON todo(todo_type);
CREATE INDEX idx_todo_due_date ON todo(due_date) WHERE due_date IS NOT NULL;
CREATE INDEX idx_todo_reporter_id ON todo(reporter_id);

-- Excel-待办关联索引
CREATE INDEX idx_excel_todo_excel_id ON excel_todo_rel(excel_id);
CREATE INDEX idx_excel_todo_todo_id ON excel_todo_rel(todo_id);

-- 笔记相关索引
CREATE INDEX idx_note_project_id ON note(project_id);
CREATE INDEX idx_note_todo_id ON note(todo_id);
CREATE INDEX idx_note_author_id ON note(author_id);
CREATE INDEX idx_note_tags ON note USING gin((string_to_array(tags, ',')));

-- 审计日志索引
CREATE INDEX idx_audit_log_operator_id ON audit_log(operator_id);
CREATE INDEX idx_audit_log_resource ON audit_log(resource_type, resource_id);
CREATE INDEX idx_audit_log_create_time ON audit_log(create_time);

-- 离线操作索引
CREATE INDEX idx_offline_operation_entity_type ON offline_operation(entity_type);
CREATE INDEX idx_offline_operation_operation_type ON offline_operation(operation_type);
CREATE INDEX idx_offline_operation_synced ON offline_operation(synced);
CREATE INDEX idx_offline_operation_created_at ON offline_operation(created_at);

-- 本地缓存索引
CREATE UNIQUE INDEX uk_local_cache_key ON local_cache(cache_key);
CREATE INDEX idx_local_cache_type ON local_cache(cache_type);
CREATE INDEX idx_local_cache_expires_at ON local_cache(expires_at);

-- 同步状态索引
CREATE UNIQUE INDEX uk_sync_status_entity_type ON sync_status(entity_type);
CREATE INDEX idx_sync_status_sync_status ON sync_status(sync_status);
CREATE INDEX idx_sync_status_next_sync_time ON sync_status(next_sync_time);

-- 离线数据统计索引
CREATE UNIQUE INDEX uk_offline_stats_date_type ON offline_stats(stat_date, entity_type);
CREATE INDEX idx_offline_stats_entity_type ON offline_stats(entity_type);
CREATE INDEX idx_offline_stats_stat_date ON offline_stats(stat_date);

-- 数据库连接配置索引
CREATE INDEX idx_db_connection_project_id ON db_connection(project_id);
CREATE INDEX idx_db_connection_created_by ON db_connection(created_by);
CREATE INDEX idx_db_connection_db_type ON db_connection(db_type);
CREATE UNIQUE INDEX uk_db_connection_name_project ON db_connection(connection_name, project_id);

-- 数据表映射配置索引
CREATE INDEX idx_table_mapping_connection_id ON table_mapping(connection_id);
CREATE UNIQUE INDEX uk_table_mapping_source_target ON table_mapping(connection_id, source_table_name, target_table_name);

-- 数据同步记录索引
CREATE INDEX idx_data_sync_record_connection_id ON data_sync_record(connection_id);
CREATE INDEX idx_data_sync_record_table_name ON data_sync_record(table_name);
CREATE INDEX idx_data_sync_record_status ON data_sync_record(status);
CREATE INDEX idx_data_sync_record_created_at ON data_sync_record(created_at);

-- 全文搜索索引 ------------- 失败
CREATE INDEX idx_project_fulltext ON project 
USING gin(to_tsvector('chinese', project_name || ' ' || COALESCE(description, '')));

CREATE INDEX idx_todo_fulltext ON todo 
USING gin(to_tsvector('chinese', title || ' ' || COALESCE(description, '')));

CREATE INDEX idx_note_fulltext ON note 
USING gin(to_tsvector('chinese', title || ' ' || content));

-- 说明: 创建自动更新时间戳的触发器

-- 创建自动更新时间戳的函数
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为需要自动更新时间戳的表创建触发器
CREATE TRIGGER update_sys_user_modtime 
    BEFORE UPDATE ON sys_user 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

CREATE TRIGGER update_project_modtime 
    BEFORE UPDATE ON project 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

CREATE TRIGGER update_todo_modtime 
    BEFORE UPDATE ON todo 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

CREATE TRIGGER update_excel_plan_modtime 
    BEFORE UPDATE ON excel_plan 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

CREATE TRIGGER update_note_modtime 
    BEFORE UPDATE ON note 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- 创建数据库连接相关表的自动更新时间戳触发器
CREATE OR REPLACE FUNCTION update_db_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_db_connection_modtime 
    BEFORE UPDATE ON db_connection 
    FOR EACH ROW EXECUTE FUNCTION update_db_modified_column();

CREATE TRIGGER update_table_mapping_modtime 
    BEFORE UPDATE ON table_mapping 
    FOR EACH ROW EXECUTE FUNCTION update_db_modified_column();